﻿using System.Collections.Generic;
using System.Data;

namespace DbMetadata.Providers.SqlServer
{
    
    /// <summary>
    /// Extracts metadata from a Microsoft SQL Server database.
    /// </summary>
    public class SqlMetadataExtractor : DbMetadataExtractor
    {
       
        /// <summary>
        /// Initializes a new instance of the <see cref="SqlMetadataExtractor"/> class.
        /// </summary>
        /// <param name="dbConnection"></param>
        public SqlMetadataExtractor(IDbConnection dbConnection) : base(dbConnection)
        {
        }
        
        /// <summary>
        /// Returns a list of <see cref="IEnumerable{T}"/> instances.
        /// </summary>
        /// <returns>A <see cref="TableInfo"/> that contains <see cref="TableInfo"/> instances.</returns>
        public override IEnumerable<TableInfo> ReadTables()
        {
            var reader = ExecuteReader(Sql.Tables);
            IList<TableInfo> tables = new List<TableInfo>();
            while (reader.Read())
            {
                var tableInfo = new TableInfo();
                tableInfo.TableCatalog = reader.GetString(0);
                tableInfo.TableSchema = reader.GetString(1);
                tableInfo.TableName = reader.GetString(2);
                string tableType = reader.GetString(3);
                tableInfo.TableType = tableType == "BASE TABLE" ? "TABLE" : "VIEW";
                tables.Add(tableInfo);
            }
            reader.Close();
            return tables;
        }

        /// <summary>
        /// Returns a list of <see cref="ColumnInfo"/> instances.
        /// </summary>
        /// <returns>A <see cref="IEnumerable{T}"/> that contains <see cref="ColumnInfo"/> instances.</returns>
        public override IEnumerable<ColumnInfo> ReadColumns()
        {
            var reader = ExecuteReader(Sql.Columns);
            IList<ColumnInfo> columns = new List<ColumnInfo>();
            while (reader.Read())
            {
                var columnInfo = new ColumnInfo();
                columnInfo.TableCatalog = reader.GetString(0);
                columnInfo.TableSchema = reader.GetString(1);
                columnInfo.TableName = reader.GetString(2);
                columnInfo.ColumnName = reader.GetString(3);
                columnInfo.OrdinalPosition = reader.GetInt32(4);
                columnInfo.DataTypeName = reader.GetString(5);
                columnInfo.IsNullable = reader.GetInt32(6) == 1 ? true : false;
                columns.Add(columnInfo);
            }
            reader.Close();
            return columns;
        }

        /// <summary>
        /// Returns a list of <see cref="ForeignKeyInfo"/> instances.
        /// </summary>
        /// <returns>A <see cref="IEnumerable{T}"/> that contains <see cref="ForeignKeyInfo"/> instances.</returns>
        public override IEnumerable<ForeignKeyInfo> ReadForeignKeys()
        {
            var reader = ExecuteReader(Sql.ForeignKeys);
            IList<ForeignKeyInfo> foreignKeys = new List<ForeignKeyInfo>();
            while (reader.Read())
            {
                var foreignkeyInfo = new ForeignKeyInfo();
                foreignkeyInfo.ConstraintName = reader.GetString(0);
                foreignkeyInfo.ForeignTableCatalog = reader.GetString(1);
                foreignkeyInfo.ForeignTableSchema = reader.GetString(2);
                foreignkeyInfo.ForeignTableName = reader.GetString(3);
                foreignkeyInfo.ForeignColumnName = reader.GetString(4);
                foreignkeyInfo.PrimaryTableCatalog = reader.GetString(5);
                foreignkeyInfo.PrimaryTableSchema = reader.GetString(6);
                foreignkeyInfo.PrimaryTableName = reader.GetString(7);
                foreignkeyInfo.PrimaryColumnName = reader.GetString(8);
                foreignKeys.Add(foreignkeyInfo);
            }
            reader.Close();
            return foreignKeys;
        }

        /// <summary>
        /// Returns a list of <see cref="PrimaryKeyInfo"/> instances.
        /// </summary>
        /// <returns>A <see cref="IEnumerable{T}"/> that contains <see cref="PrimaryKeyInfo"/> instances.</returns>
        public override IEnumerable<PrimaryKeyInfo> ReadPrimaryKeys()
        {
            var reader = ExecuteReader(Sql.PrimaryKeys);
            IList<PrimaryKeyInfo> primaryKeys = new List<PrimaryKeyInfo>();
            while (reader.Read())
            {
                var primaryKeyInfo = new PrimaryKeyInfo();
                primaryKeyInfo.ConstraintName = reader.GetString(0);
                primaryKeyInfo.TableCatalog = reader.GetString(1);
                primaryKeyInfo.TableSchema = reader.GetString(2);
                primaryKeyInfo.TableName = reader.GetString(3);
                primaryKeyInfo.ColumnName = reader.GetString(4);
                primaryKeys.Add(primaryKeyInfo);
            }
            reader.Close();
            return primaryKeys;
        }
    }
}
